<?php
/*
 * @Author: your name
 * @Date: 2020-04-09 17:00:11
 * @LastEditTime: 2020-04-28 15:14:46
 * @LastEditors: Please set LastEditors
 * @Description: In User Settings Edit
 * @FilePath: \ztaudio-activity\app\Http\Model\UserM.php
 */

namespace App\Http\Model;

use Illuminate\Support\Facades\DB;

class UserM extends User
{
    protected $table = "user";
    protected $table_voices = 'voices';
    
    public function __construct(array $attributes = [])
    {
        parent::__construct();
    }

    public function getPortraitAttribute($value)
    {
	      $res = json_decode($value,true)[0]??'';
        if(empty($res)) return '';
        return build_image($res);
    }

    public function getCreateTimestampAttribute($value) {
      return $value ? date('Y年m月d日 H:i', $value) : '';
    }

    public function getInviteUserInfo($user_id) {
      return self::where('user_id', $user_id)->first();
    }

    /**
     * 根据邀请码获取用户信息
     *
     * @param string $code
     * @return void
     */
    public function getUserInfoByCode(string $code) {
      return self::where("code", $code)->first(['nickname', 'portrait', 'user_id']);
    }

    public function getFiveWinners($user_ids = []) {
      $query = new self();
      if(!empty($user_ids)) $query = $query->whereIn('user_id', $user_ids);
      return $query->get(['nickname', 'uuid', 'portrait', 'user_id']);
    }
    /**
     * 五月联赛 语音列表
     *
     * @param 
     * @return void
     */
    public function getFiveVoice($page, $limit,$search) {
      $arr =explode('=',$search);
      // dd($arr);
      if(empty($arr[0])) {
        $sql = "SELECT
          a.id,
          a.user_id,
          a.audio_url,
          a.audio_time,
          a.ret,
          a.gender,
          u.nickname,
          u.portrait
        FROM
          zb_voices a
        INNER JOIN (
          SELECT
            user_id,
            max(id) AS id,
            max(created_at) AS created_at
          FROM
            `zb_voices`
          GROUP BY
            user_id
          ORDER BY
            user_id DESC
        ) b ON a.id = b.id
        INNER JOIN zb_user as u ON u.user_id = a.user_id LIMIT {$page},{$limit}";
      }elseif($arr[0] == 'user_id'){
        $sql = "SELECT
          a.id,
          a.user_id,
          a.audio_url,
          a.audio_time,
          a.ret,
          a.gender,
          u.nickname,
          u.portrait
        FROM
          zb_voices a
        INNER JOIN (
          SELECT
            user_id,
            max(id) AS id,
            max(created_at) AS created_at
          FROM
            `zb_voices`
          WHERE
             {$search}
          GROUP BY
            user_id
          ORDER BY
            user_id DESC
        ) b ON a.id = b.id
        INNER JOIN zb_user as u ON u.user_id = a.user_id ";
      }else{
        $sql = "SELECT
          a.id,
          a.user_id,
          a.audio_url,
          a.audio_time,
          a.ret,
          a.gender,
          u.nickname,
          u.portrait
        FROM
          zb_voices a
        INNER JOIN (
          SELECT
            user_id,
            max(id) AS id,
            max(created_at) AS created_at
          FROM
            `zb_voices`
          GROUP BY
            user_id
          ORDER BY
            user_id DESC
        ) b ON a.id = b.id
        INNER JOIN zb_user as u ON u.user_id = a.user_id WHERE {$search} LIMIT {$page},{$limit}";
      } 
        $data = DB::connection('mysql_kkaudio')->select($sql);

        $data = self::objToArray($data);

        foreach($data as &$item) {
          $portrait = json_decode($item['portrait'], true);
          $item['portrait'] = build_image($portrait[0]);
          $item['audio_url'] = build_image($item['audio_url']);
          $item['ret'] = json_decode($item['ret'], true);
        }
        unset($item);
        return $data;
    }
    public function voiceCount($user_id = []){
      $data = DB::connection('mysql_kkaudio')
      ->table('voices')
      ->whereIn('user_id', $user_id)
      ->get();
      return self::objToArray($data);
    }
    //海选淘汰人员名单
    public function voiceAll($user_id = []){
      $sql = "SELECT
          a.id,
          a.user_id,
          a.audio_url,
          a.audio_time,
          a.ret,
          a.gender
        FROM
          zb_voices a
        INNER JOIN (
          SELECT
            user_id,
            max(id) AS id,
            max(created_at) AS created_at
          FROM
            `zb_voices`
          GROUP BY
            user_id
          ORDER BY
            user_id DESC
        ) b ON a.id = b.id";
        $data = DB::connection('mysql_kkaudio')->select($sql);
      // $data = DB::connection('mysql_kkaudio')
      // ->table('voices')
      // ->whereNotIn('user_id', $user_id)
      // ->get();
      return self::objToArray($data);
    }
    public function getFiveRich(){
      $sql = "select a.from_user_id,sum(a.gift_num) as gift_num,b.nickname,b.portrait,b.uuid,b.uuid from zb_gift_stream a
        left join zb_user b on a.from_user_id = b.user_id
        where a.gift_id=185 and a.change_timestamp >= 1590422400 group by a.from_user_id order by gift_num desc;";
      $data =  DB::connection('mysql_kkaudio')->select($sql);
      return self::objToArray($data);
    }
    public function getSenderList(){
      $sql = "select a.from_user_id,sum(a.gift_num) as gift_num,b.nickname,b.uuid from zb_gift_stream a
        left join  zb_user b on a.from_user_id = b.user_id
        where a.gift_id=185 and a.change_timestamp >= 1590422400 group by a.from_user_id order by gift_num desc;";
      $data =  DB::connection('mysql_kkaudio')->select($sql);
      return self::objToArray($data);
    }
    public function getSenderList1(){
      $sql = "select a.user_id,sum(a.gift_num) as gift_num,b.nickname,b.uuid from zb_gift_stream a
      left join zb_user b on a.user_id = b.user_id
      where a.gift_id=185 and a.change_timestamp >= 1590422400 group by a.user_id order by gift_num desc;";
      $data =  DB::connection('mysql_kkaudio')->select($sql);
      return self::objToArray($data);
    }
}
